﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using System.Data;

namespace DAL
{
   public class OrderDAL
    {
        /// <summary>
        /// 订单
        /// </summary>
        //订单添加
        public static int orderAdd(Order o)
        {
            string sql = "insert into tb_Order(Ordertitle,OrderName,Phone,PayID,UserID,Pay,Address,Time,Note) values(@Ordertitle,@OrderName,@Phone,@PayID,@UserID,@Pay,@Address,@Time,@Note)";
            SqlParameter[] para = {
            new SqlParameter("@Ordertitle",o.Ordertitle),
            new SqlParameter("@OrderName",o.OrderName),
            new SqlParameter("@Phone",o.Phone),
            new SqlParameter("@PayID",o.PayID),
            new SqlParameter("@UserID",o.UserID),
            new SqlParameter("@Pay",o.Pay),
            new SqlParameter("@Address",o.Address),
            new SqlParameter("@Time",o.Time),
            new SqlParameter("@Note",o.Note),
            };
            return SqlDBHelper.ExecuteNonQuery(sql,para);
        }
        //有订单编号查询订单id
        public static object orderID(string bh) {
            string sql = "select OrderID from tb_Order where Ordertitle=" + bh;
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //订单支付
        public static int orderPay(string bh)
        {
            string sql = "update tb_Order set Zf='已付款' where Ordertitle="+bh;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //后台订单查询
        public static DataTable orderCX() {
            string sql = @"select  DISTINCT o.OrderID,o.Ordertitle,o.OrderName,o.Phone,o.Pay,o.Zf,o.Fh,o.Sh,p.PayName,o.Address,o.Logistics,o.Note,o.Time
                            from tb_Order o,tb_Pay p where o.PayID=p.PayID";
            return SqlDBHelper.GetDataTable(sql);
        }
        //订单发货判断是否支付
        public static object ddSfzf(int id)
        {
            string sql = "select Zf from tb_Order where OrderID="+id;
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //订单发货
        public static int orderGoods(int id)
        {
            string sql = "update tb_Order set Fh='已发货' where OrderID="+id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //确认订单是否收货
        public static object orderSFSH(int id)
        {
            string sql = "select Fh from tb_Order where OrderID=" + id;
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //订单收货
        public static int receiveGoods(int id)
        {
            string sql = "update tb_Order set Sh='已收货' where OrderID="+ id;
            return SqlDBHelper.ExecuteNonQuery(sql);
        }
        //查询数据条数
        public static object dataNum()
        {
            string sql = "select count(*) from tb_Order";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //查询总金额
        public static object sum()
        {
            string sql = "select sum(Pay) from tb_Order";
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //后台订单详细查询
        public static DataTable orderDetailed(int id)
        {
            string sql = @"select  DISTINCT o.OrderID,o.Ordertitle,o.OrderName,o.Phone,o.Pay,o.Zf,o.Fh,o.Sh,p.PayName,o.Address,o.Logistics,o.Note,o.Time
                            from tb_Order o,tb_Pay p where o.PayID=p.PayID and o.OrderID="+id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //根据不同的订单要求模糊查询
        public static DataTable orderMH(string sq)
        {
            string sql = @"select  DISTINCT o.OrderID,o.Ordertitle,o.OrderName,o.Phone,o.Pay,o.Zf,o.Fh,o.Sh,p.PayName,o.Address,o.Logistics,o.Note,o.Time
                            from tb_Order o,tb_Pay p where o.PayID = p.PayID " + sq;
            return SqlDBHelper.GetDataTable(sql);
        }
        //前台我的订单查看
        public static DataTable orderQT(int id)
        {
            string sql = @"select  DISTINCT o.OrderID,o.Ordertitle,o.OrderName,o.Phone,o.Pay,o.Zf,o.Fh,o.Sh,p.PayName,o.Address,o.Logistics,o.Note,o.Time
                            from tb_Order o,tb_Pay p where o.PayID=p.PayID and o.UserID=" + id;
            return SqlDBHelper.GetDataTable(sql);
        }
        //前台订单删除
        public static int orderDelete(int id)
        {
            string sql = "delete from tb_Order where OrderID="+id;
            return SqlDBHelper.ExecuteNonQuery(sql);
           
        }
        //查询是否收货
        public static object orderSH(string id)
        {
            string sql = "select Sh from tb_Order where Ordertitle=" + id;
            return SqlDBHelper.ExecuteScalar(sql);
        }
        //根据不同的订单要求模糊查询_个人主页
        public static DataTable orderMHGR(string sq)
        {
            string sql = @"select  DISTINCT o.OrderID,o.Ordertitle,o.OrderName,o.Phone,o.Pay,o.Zf,o.Fh,o.Sh,p.PayName,o.Address,o.Logistics,o.Note,o.Time
                            from tb_Order o,tb_Pay p where o.PayID = p.PayID " + sq;
            return SqlDBHelper.GetDataTable(sql);
        }
    }
}
